insert overwrite table bidefault.dm_agency_province_pre_reach_rate_dt partition(dt)
select taking_date as date_time  -- "日期",
  ,nvl(island_code,'--') -- "订单来源",
  ,districtprescription -- "区县时效",
  ,is_pres_sign -- "时效签收",
  ,is_real_sign -- "实际签收",
  ,start_subordinate_agent_code -- "始发代理区code",
  ,sign_subordinate_agent_code -- "签收代理区code",
  ,start_provider_id -- "始发省份id",
  ,sign_provider_id -- "签收省份id",
  ,start_manage_regional_code  -- 始发管理大区code',
  ,start_manage_regional_name  -- 始发管理大区name',
  ,start_subordinate_agent_name -- "始发代理区name",
  ,start_regional_id -- "始发大区id",
  ,start_regional_desc -- "始发大区name",
  ,start_provider_desc -- "始发省份name",
  ,sign_manage_regional_code  -- 签收管理大区code',
  ,sign_manage_regional_name -- 签收管理大区name',
  ,sign_subordinate_agent_name -- "签收代理区name",
  ,sign_regional_id -- "签收大区id",
  ,sign_regional_desc -- "签收大区name",
  ,sign_provider_desc -- "签收省份name",
  ,nvl(island_name,'--')
  ,economic_circle_type  -- 经济圈名称
  ,sum(case when is_real_standard=1 and is_real_sign=1 then 1 else 0 end) as real_reach_sum -- "实际达成票数",
  ,sum(case when (is_real_standard=0 or is_real_standard is null) and (is_real_sign=1 or is_real_sign=0) then 1 else 0 end) as real_no_reach_sum -- "实际未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) then 1 else 0 end) as real_total -- "实际总票量",
  ,sum(case when is_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as pres_reach_sum -- "时效达成票数",
  ,sum(case when (is_pres_standard=0 or is_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) then 1 else 0 end) as pres_no_reach_sum -- "时效未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) then 1 else 0 end) as pres_total -- "时效总票量",
  ,sum(case when datediff(real_sign_time,taking_date)=0 then 1 else 0 end) as real_zeroday_num   --实际当日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=1 then 1 else 0 end) as real_oneday_num    --实际次日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=2 then 1 else 0 end) as real_twoday_num    --实际隔日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=3 then 1 else 0 end) as real_threeday_num  --实际三日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=4 then 1 else 0 end) as real_four_num      --实际四日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=0 then 1 else 0 end) as pres_zeroday_num  --时效当日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=1 then 1 else 0 end) as pres_oneday_num  --时效次日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=2 then 1 else 0 end) as pres_twoday_num  --时效隔日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=3 then 1 else 0 end) as pres_threeday_num  --时效三日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=4 then 1 else 0 end) as pres_four_num  --时效四日达票数
  --桃花岛平台时效
  ,platform_effect_type  -- 是否平台时效
  ,sum(case when is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as real_platform_reach_sum -- "实际桃花岛达成票数",
  ,sum(case when (is_platform_real_standard=0 or is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and platform_effect_type=1 then 1 else 0 end) as real_platform_no_reach_sum -- "实际桃花岛未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and platform_effect_type=1 then 1 else 0 end) as real_platform_total -- "实际桃花岛总票量",
  ,sum(case when is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as pres_platform_reach_sum -- "时效桃花岛达成票数",
  ,sum(case when (is_platform_pres_standard=0 or is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and platform_effect_type=1 then 1 else 0 end) as pres_platform_no_reach_sum -- "时效桃花岛未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and platform_effect_type=1 then 1 else 0 end) as pres_platform_total -- "时效桃花岛总票量",
  ,platform_name  --桃花岛平台时效名称
  ,platform_effect  -- 平台时效
  ,plan_platform_date
  --紫金山平台时效
  ,sum(case when mountain_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as mountain_real_platform_reach_sum -- "实际紫金山达成票数",
  ,sum(case when (mountain_is_platform_real_standard=0 or mountain_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_real_platform_no_reach_sum -- "实际紫金山未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_real_platform_total -- "实际紫金山总票量",
  ,sum(case when mountain_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as mountain_pres_platform_reach_sum -- "时效紫金山达成票数",
  ,sum(case when (mountain_is_platform_pres_standard=0 or mountain_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_pres_platform_no_reach_sum -- "时效紫金山未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_pres_platform_total -- "时效紫金山总票量",
  ,mountain_platform_name  --紫金山平台时效名称
  ,mountain_platform_effect  -- 紫金山平台时效
  ,plan_mountain_platform_date
  --逍遥峰平台时效
  ,sum(case when free_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as free_real_platform_reach_sum -- "实际逍遥峰达成票数",
  ,sum(case when (free_is_platform_real_standard=0 or free_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and free_platform_effect_type=1 then 1 else 0 end) as free_real_platform_no_reach_sum -- "实际逍遥峰未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and free_platform_effect_type=1 then 1 else 0 end) as free_real_platform_total -- "实际逍遥峰总票量",
  ,sum(case when free_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as free_pres_platform_reach_sum -- "时效逍遥峰达成票数",
  ,sum(case when (free_is_platform_pres_standard=0 or free_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and free_platform_effect_type=1 then 1 else 0 end) as free_pres_platform_no_reach_sum -- "时效逍遥峰未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and free_platform_effect_type=1 then 1 else 0 end) as free_pres_platform_total -- "时效逍遥峰总票量",
  ,free_platform_name  --逍遥峰平台时效名称
  ,free_platform_effect  -- 逍遥锋平台时效
  ,plan_free_platform_date
    --京东平台时效
  ,sum(case when jd_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as jd_real_platform_reach_sum -- "实际京东达成票数",
  ,sum(case when (jd_is_platform_real_standard=0 or jd_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and jd_platform_effect_type=1 then 1 else 0 end) as jd_real_platform_no_reach_sum -- "实际京东未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and jd_platform_effect_type=1 then 1 else 0 end) as jd_real_platform_total -- "实际京东总票量",
  ,sum(case when jd_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as jd_pres_platform_reach_sum -- "时效京东达成票数",
  ,sum(case when (jd_is_platform_pres_standard=0 or jd_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and jd_platform_effect_type=1 then 1 else 0 end) as jd_pres_platform_no_reach_sum -- "时效京东未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and jd_platform_effect_type=1 then 1 else 0 end) as jd_pres_platform_total -- "时效京东总票量",
  ,jd_platform_name  --京东平台时效名称
  ,jd_platform_effect  -- 逍遥锋平台时效
  ,date(plan_jd_platform_date) plan_jd_platform_date
    --菜鸟平台时效
  ,sum(case when cainiao_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as cainiao_real_platform_reach_sum -- "实际菜鸟达成票数",
  ,sum(case when (cainiao_is_platform_real_standard=0 or cainiao_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_real_platform_no_reach_sum -- "实际菜鸟未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_real_platform_total -- "实际菜鸟总票量",
  ,sum(case when cainiao_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as cainiao_pres_platform_reach_sum -- "时效菜鸟达成票数",
  ,sum(case when (cainiao_is_platform_pres_standard=0 or cainiao_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_pres_platform_no_reach_sum -- "时效菜鸟未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_pres_platform_total -- "时效菜鸟总票量",
  ,cainiao_platform_name  --菜鸟平台时效名称
  ,cainiao_platform_effect  -- 逍遥锋平台时效
  ,plan_cainiao_platform_date
 --菜鸟超时平台时效
  ,sum(case when cainiao_pro_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as cainiao_pro_real_platform_reach_sum -- "实际菜鸟达成票数",
  ,sum(case when (cainiao_pro_is_platform_real_standard=0 or cainiao_pro_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_real_platform_no_reach_sum -- "实际菜鸟未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_real_platform_total -- "实际菜鸟总票量",
  ,sum(case when cainiao_pro_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as cainiao_pro_pres_platform_reach_sum -- "时效菜鸟达成票数",
  ,sum(case when (cainiao_pro_is_platform_pres_standard=0 or cainiao_pro_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_pres_platform_no_reach_sum -- "时效菜鸟未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_pres_platform_total -- "时效菜鸟总票量",
  ,cainiao_pro_platform_name  --菜鸟超时平台时效名称
  ,cainiao_pro_platform_effect  -- 菜鸟超时平台时效
  ,plan_cainiao_pro_platform_date
  ,express_type_code
  ,express_type_name
  ,sum(case when datediff(real_sign_time,taking_date)=5 then 1 else 0 end) as real_five_num    --实际五日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=6 then 1 else 0 end) as real_six_num  --实际六日达票数
  ,sum(case when datediff(real_sign_time,taking_date)>6 then 1 else 0 end) as real_six_up_num  --实际六日以上达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=5 then 1 else 0 end) as pre_five_num  --时效五日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=6 then 1 else 0 end) as pre_six_num  --时效六日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)>6 then 1 else 0 end) as pre_six_up_num  --时效六日以上达票数
  ,sum(if(cainiao_slower_pre=1,1,0)) as cainiao_slower_pre_num --菜鸟慢于平台的票数
  ,customer_code
  ,customer_name
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>0 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=24,1,0)) as jd_real_one_num    --实际24票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>24 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=36 ,1,0)) as jd_real_two_num  --实际36票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>36 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=48 ,1,0)) as jd_real_three_num  --实际48票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>48 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=60 ,1,0)) as jd_real_four_num  --实际60票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>60 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=72 ,1,0)) as jd_real_five_num  --实际72票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>72 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=96 ,1,0)) as jd_real_six_num  --实际96票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>96 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=120,1,0)) as jd_real_seven_num  --实际120票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>120,1,0)) as jd_real_eight_num    --实际大于120票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>0 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=24,1,0)) as jd_pres_one_num      --实际24票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>24 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=36 ,1,0)) as jd_pres_two_num    --实际36票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>36 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=48 ,1,0)) as jd_pres_three_num  --实际48票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>48 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=60 ,1,0)) as jd_pres_four_num   --实际60票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>60 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=72 ,1,0)) as jd_pres_five_num   --实际72票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>72 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=96 ,1,0)) as jd_pres_six_num    --实际96票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>96 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=120,1,0)) as jd_pres_seven_num  --实际120票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>120,1,0)) as jd_pres_eight_num    --实际大于120票数
  ,sum(if(cainiao_pro_real_effect_diff=2,1,0))  cainiao_pro_real_pre_two_num
  ,sum(if(cainiao_pro_real_effect_diff=1,1,0))  cainiao_pro_real_pre_one_num
  ,sum(if(cainiao_pro_real_effect_diff=-1,1,0)) cainiao_pro_real_late_one_num
  ,sum(if(cainiao_pro_real_effect_diff=-2,1,0)) cainiao_pro_real_late_two_num
  ,sum(if(cainiao_pro_real_effect_diff<-2,1,0)) cainiao_pro_real_late_two_more_num
  ,sum(if(cainiao_pro_pres_effect_diff=2,1,0))  cainiao_pro_pres_pre_two_num
  ,sum(if(cainiao_pro_pres_effect_diff=1,1,0))  cainiao_pro_pres_pre_one_num
  ,sum(if(cainiao_pro_pres_effect_diff=-1,1,0)) cainiao_pro_pres_late_one_num
  ,sum(if(cainiao_pro_pres_effect_diff=-2,1,0)) cainiao_pro_pres_late_two_num
  ,sum(if(cainiao_pro_pres_effect_diff<-2,1,0)) cainiao_pro_pres_late_two_more_num
  --紫金山平台时效
  ,sum(case when mountain_pro_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as mountain_pro_real_platform_reach_sum -- "实际紫金山达成票数",
  ,sum(case when (mountain_pro_is_platform_real_standard=0 or mountain_pro_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_real_platform_no_reach_sum -- "实际紫金山未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_real_platform_total -- "实际紫金山总票量",
  ,sum(case when mountain_pro_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as mountain_pro_pres_platform_reach_sum -- "时效紫金山达成票数",
  ,sum(case when (mountain_pro_is_platform_pres_standard=0 or mountain_pro_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_pres_platform_no_reach_sum -- "时效紫金山未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_pres_platform_total -- "时效紫金山总票量",
  ,mountain_pro_platform_name  --紫金山平台时效名称
  ,mountain_pro_platform_effect  -- 紫金山平台时效
  ,plan_mountain_pro_platform_date
  ,taking_date as dt
FROM bidefault.dm_prescription_reach_details_dt where dt>=date_sub('{{ execution_date | cst_ds }}',10) and dt<='{{ execution_date | cst_ds }}' and taking_date>=date_sub('{{ execution_date | cst_ds }}',10) and taking_date<='{{ execution_date | cst_ds }}'
GROUP BY taking_date
  ,island
  ,nvl(island_code,'--')
  ,nvl(island_name,'--')
  ,districtprescription
  ,start_subordinate_agent_code
  ,start_subordinate_agent_name
  ,start_regional_id
  ,start_regional_desc
  ,start_provider_id
  ,start_provider_desc
  ,sign_subordinate_agent_code
  ,sign_subordinate_agent_name
  ,sign_regional_id
  ,sign_regional_desc
  ,sign_provider_id
  ,sign_provider_desc
  ,is_pres_sign
  ,is_real_sign
  ,economic_circle_type  -- 经济圈名称
  ,platform_effect_type  -- 是否平台时效
  ,platform_effect  -- 平台时效
  ,platform_name  --桃花岛平台时效名称
  ,plan_platform_date
  ,mountain_platform_name  --紫金山平台时效名称
  ,plan_mountain_platform_date
  ,mountain_platform_effect  -- 紫金山平台时效
  ,free_platform_name  --逍遥锋平台时效名称
  ,plan_free_platform_date
  ,free_platform_effect  -- 逍遥锋平台时效
  ,start_manage_regional_code  -- 始发管理大区code',
  ,start_manage_regional_name  -- 始发管理大区name',
  ,sign_manage_regional_code  -- 签收管理大区code',
  ,sign_manage_regional_name -- 签收管理大区name',
  ,jd_platform_name  --京东平台时效名称
  ,jd_platform_effect  -- 京东平台时效
  ,date(plan_jd_platform_date)
  ,cainiao_platform_name  --菜鸟平台时效名称
  ,cainiao_platform_effect  -- 菜鸟平台时效
  ,plan_cainiao_platform_date
  ,cainiao_pro_platform_name  --菜鸟超时平台时效名称
  ,cainiao_pro_platform_effect  -- 菜鸟超时平台时效
  ,plan_cainiao_pro_platform_date
  ,express_type_code
  ,express_type_name
  ,customer_code
  ,customer_name
  ,mountain_pro_platform_name  --紫金山平台时效名称
  ,mountain_pro_platform_effect  -- 紫金山平台时效
  ,plan_mountain_pro_platform_date
  distribute by dt, PMOD(HASH(RAND()),80);


insert overwrite table bidefault.dm_agency_province_pre_reach_rate_plan_dt partition(dt)
select from_unixtime(unix_timestamp(plan_sign_date),'yyyy-MM-dd') as date_time  -- "日期",
  ,nvl(island_code,'--') -- "订单来源",
  ,districtprescription -- "区县时效",
  ,is_pres_sign -- "时效签收",
  ,is_real_sign -- "实际签收",
  ,start_subordinate_agent_code -- "始发代理区code",
  ,sign_subordinate_agent_code -- "签收代理区code",
  ,start_provider_id -- "始发省份id",
  ,sign_provider_id -- "签收省份id",
  ,start_manage_regional_code  -- 始发管理大区code',
  ,start_manage_regional_name  -- 始发管理大区name',
  ,start_subordinate_agent_name -- "始发代理区name",
  ,start_regional_id -- "始发大区id",
  ,start_regional_desc -- "始发大区name",
  ,start_provider_desc -- "始发省份name",
  ,sign_manage_regional_code  -- 签收管理大区code',
  ,sign_manage_regional_name -- 签收管理大区name',
  ,sign_subordinate_agent_name -- "签收代理区name",
  ,sign_regional_id -- "签收大区id",
  ,sign_regional_desc -- "签收大区name",
  ,sign_provider_desc -- "签收省份name",
  ,nvl(island_name,'--')
  ,economic_circle_type  -- 经济圈名称
  ,sum(case when is_real_standard=1 and is_real_sign=1 then 1 else 0 end) as real_reach_sum -- "实际达成票数",
  ,sum(case when (is_real_standard=0 or is_real_standard is null) and (is_real_sign=1 or is_real_sign=0) then 1 else 0 end) as real_no_reach_sum -- "实际未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) then 1 else 0 end) as real_total -- "实际总票量",
  ,sum(case when is_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as pres_reach_sum -- "时效达成票数",
  ,sum(case when (is_pres_standard=0 or is_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) then 1 else 0 end) as pres_no_reach_sum -- "时效未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) then 1 else 0 end) as pres_total -- "时效总票量",
  ,sum(case when datediff(real_sign_time,taking_date)=0 then 1 else 0 end) as real_zeroday_num   --实际当日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=1 then 1 else 0 end) as real_oneday_num    --实际次日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=2 then 1 else 0 end) as real_twoday_num    --实际隔日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=3 then 1 else 0 end) as real_threeday_num  --实际三日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=4 then 1 else 0 end) as real_four_num      --实际四日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=0 then 1 else 0 end) as pres_zeroday_num  --时效当日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=1 then 1 else 0 end) as pres_oneday_num  --时效次日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=2 then 1 else 0 end) as pres_twoday_num  --时效隔日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=3 then 1 else 0 end) as pres_threeday_num  --时效三日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=4 then 1 else 0 end) as pres_four_num  --时效四日达票数
   --桃花岛平台时效
  ,platform_effect_type  -- 是否平台时效
  ,sum(case when is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as real_platform_reach_sum -- "实际桃花岛达成票数",
  ,sum(case when (is_platform_real_standard=0 or is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and platform_effect_type=1 then 1 else 0 end) as real_platform_no_reach_sum -- "实际桃花岛未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and platform_effect_type=1 then 1 else 0 end) as real_platform_total -- "实际桃花岛总票量",
  ,sum(case when is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as pres_platform_reach_sum -- "时效桃花岛达成票数",
  ,sum(case when (is_platform_pres_standard=0 or is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and platform_effect_type=1 then 1 else 0 end) as pres_platform_no_reach_sum -- "时效桃花岛未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and platform_effect_type=1 then 1 else 0 end) as pres_platform_total -- "时效桃花岛总票量",
  ,platform_name  --桃花岛平台时效名称
  ,platform_effect  -- 平台时效
  ,plan_platform_date
  --紫金山平台时效
  ,sum(case when mountain_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as mountain_real_platform_reach_sum -- "实际紫金山达成票数",
  ,sum(case when (mountain_is_platform_real_standard=0 or mountain_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_real_platform_no_reach_sum -- "实际紫金山未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_real_platform_total -- "实际紫金山总票量",
  ,sum(case when mountain_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as mountain_pres_platform_reach_sum -- "时效紫金山达成票数",
  ,sum(case when (mountain_is_platform_pres_standard=0 or mountain_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_pres_platform_no_reach_sum -- "时效紫金山未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_pres_platform_total -- "时效紫金山总票量",
  ,mountain_platform_name  --紫金山平台时效名称
  ,mountain_platform_effect  -- 紫金山平台时效
  ,plan_mountain_platform_date
  --逍遥峰平台时效
  ,sum(case when free_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as free_real_platform_reach_sum -- "实际逍遥峰达成票数",
  ,sum(case when (free_is_platform_real_standard=0 or free_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and free_platform_effect_type=1 then 1 else 0 end) as free_real_platform_no_reach_sum -- "实际逍遥峰未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and free_platform_effect_type=1 then 1 else 0 end) as free_real_platform_total -- "实际逍遥峰总票量",
  ,sum(case when free_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as free_pres_platform_reach_sum -- "时效逍遥峰达成票数",
  ,sum(case when (free_is_platform_pres_standard=0 or free_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and free_platform_effect_type=1 then 1 else 0 end) as free_pres_platform_no_reach_sum -- "时效逍遥峰未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and free_platform_effect_type=1 then 1 else 0 end) as free_pres_platform_total -- "时效逍遥峰总票量",
  ,free_platform_name  --逍遥峰平台时效名称
  ,free_platform_effect  -- 逍遥锋平台时效
  ,plan_free_platform_date
    --京东平台时效
  ,sum(case when jd_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as jd_real_platform_reach_sum -- "实际京东达成票数",
  ,sum(case when (jd_is_platform_real_standard=0 or jd_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and jd_platform_effect_type=1 then 1 else 0 end) as jd_real_platform_no_reach_sum -- "实际京东未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and jd_platform_effect_type=1 then 1 else 0 end) as jd_real_platform_total -- "实际京东总票量",
  ,sum(case when jd_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as jd_pres_platform_reach_sum -- "时效京东达成票数",
  ,sum(case when (jd_is_platform_pres_standard=0 or jd_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and jd_platform_effect_type=1 then 1 else 0 end) as jd_pres_platform_no_reach_sum -- "时效京东未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and jd_platform_effect_type=1 then 1 else 0 end) as jd_pres_platform_total -- "时效京东总票量",
  ,jd_platform_name  --京东平台时效名称
  ,jd_platform_effect  -- 逍遥锋平台时效
  ,date(plan_jd_platform_date) plan_jd_platform_date
    --菜鸟平台时效
  ,sum(case when cainiao_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as cainiao_real_platform_reach_sum -- "实际菜鸟达成票数",
  ,sum(case when (cainiao_is_platform_real_standard=0 or cainiao_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_real_platform_no_reach_sum -- "实际菜鸟未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_real_platform_total -- "实际菜鸟总票量",
  ,sum(case when cainiao_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as cainiao_pres_platform_reach_sum -- "时效菜鸟达成票数",
  ,sum(case when (cainiao_is_platform_pres_standard=0 or cainiao_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_pres_platform_no_reach_sum -- "时效菜鸟未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_pres_platform_total -- "时效菜鸟总票量",
  ,cainiao_platform_name  --菜鸟平台时效名称
  ,cainiao_platform_effect  -- 逍遥锋平台时效
  ,plan_cainiao_platform_date
 --菜鸟超时平台时效
  ,sum(case when cainiao_pro_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as cainiao_pro_real_platform_reach_sum -- "实际菜鸟达成票数",
  ,sum(case when (cainiao_pro_is_platform_real_standard=0 or cainiao_pro_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_real_platform_no_reach_sum -- "实际菜鸟未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_real_platform_total -- "实际菜鸟总票量",
  ,sum(case when cainiao_pro_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as cainiao_pro_pres_platform_reach_sum -- "时效菜鸟达成票数",
  ,sum(case when (cainiao_pro_is_platform_pres_standard=0 or cainiao_pro_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_pres_platform_no_reach_sum -- "时效菜鸟未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_pres_platform_total -- "时效菜鸟总票量",
  ,cainiao_pro_platform_name  --菜鸟超时平台时效名称
  ,cainiao_pro_platform_effect  -- 菜鸟超时平台时效
  ,plan_cainiao_pro_platform_date
  ,express_type_code
  ,express_type_name
  ,sum(case when datediff(real_sign_time,taking_date)=5 then 1 else 0 end) as real_five_num    --实际五日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=6 then 1 else 0 end) as real_six_num  --实际六日达票数
  ,sum(case when datediff(real_sign_time,taking_date)>6 then 1 else 0 end) as real_six_up_num  --实际六日以上达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=5 then 1 else 0 end) as pre_five_num  --时效五日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=6 then 1 else 0 end) as pre_six_num  --时效六日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)>6 then 1 else 0 end) as pre_six_up_num  --时效六日以上达票数
  ,sum(if(cainiao_slower_pre=1,1,0)) as cainiao_slower_pre_num --菜鸟慢于平台的票数
  ,customer_code
  ,customer_name
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>0 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=24,1,0)) as jd_real_one_num    --实际24票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>24 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=36 ,1,0)) as jd_real_two_num  --实际36票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>36 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=48 ,1,0)) as jd_real_three_num  --实际48票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>48 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=60 ,1,0)) as jd_real_four_num  --实际60票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>60 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=72 ,1,0)) as jd_real_five_num  --实际72票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>72 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=96 ,1,0)) as jd_real_six_num  --实际96票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>96 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=120,1,0)) as jd_real_seven_num  --实际120票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>120,1,0)) as jd_real_eight_num    --实际大于120票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>0 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=24,1,0)) as jd_pres_one_num      --实际24票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>24 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=36 ,1,0)) as jd_pres_two_num    --实际36票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>36 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=48 ,1,0)) as jd_pres_three_num  --实际48票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>48 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=60 ,1,0)) as jd_pres_four_num   --实际60票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>60 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=72 ,1,0)) as jd_pres_five_num   --实际72票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>72 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=96 ,1,0)) as jd_pres_six_num    --实际96票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>96 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=120,1,0)) as jd_pres_seven_num  --实际120票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>120,1,0)) as jd_pres_eight_num    --实际大于120票数
  ,sum(if(cainiao_pro_real_effect_diff=2,1,0)) cainiao_pro_real_pre_two_num
  ,sum(if(cainiao_pro_real_effect_diff=1,1,0)) cainiao_pro_real_pre_one_num
  ,sum(if(cainiao_pro_real_effect_diff=-1,1,0)) cainiao_pro_real_late_one_num
  ,sum(if(cainiao_pro_real_effect_diff=-2,1,0)) cainiao_pro_real_late_two_num
  ,sum(if(cainiao_pro_real_effect_diff<-2,1,0)) cainiao_pro_real_late_two_more_num
  ,sum(if(cainiao_pro_pres_effect_diff=2,1,0))  cainiao_pro_pres_pre_two_num
  ,sum(if(cainiao_pro_pres_effect_diff=1,1,0))  cainiao_pro_pres_pre_one_num
  ,sum(if(cainiao_pro_pres_effect_diff=-1,1,0)) cainiao_pro_pres_late_one_num
  ,sum(if(cainiao_pro_pres_effect_diff=-2,1,0)) cainiao_pro_pres_late_two_num
  ,sum(if(cainiao_pro_pres_effect_diff<-2,1,0)) cainiao_pro_pres_late_two_more_num
  --紫金山平台时效
  ,sum(case when mountain_pro_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as mountain_pro_real_platform_reach_sum -- "实际紫金山达成票数",
  ,sum(case when (mountain_pro_is_platform_real_standard=0 or mountain_pro_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_real_platform_no_reach_sum -- "实际紫金山未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_real_platform_total -- "实际紫金山总票量",
  ,sum(case when mountain_pro_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as mountain_pro_pres_platform_reach_sum -- "时效紫金山达成票数",
  ,sum(case when (mountain_pro_is_platform_pres_standard=0 or mountain_pro_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_pres_platform_no_reach_sum -- "时效紫金山未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_pres_platform_total -- "时效紫金山总票量",
  ,mountain_pro_platform_name  --紫金山平台时效名称
  ,mountain_pro_platform_effect  -- 紫金山平台时效
  ,plan_mountain_pro_platform_date
  ,from_unixtime(unix_timestamp(plan_sign_date),'yyyy-MM-dd') as dt
FROM bidefault.dm_prescription_reach_details_dt where dt>=date_format(date_add(date_sub('{{ execution_date | cst_ds }}',10) ,-7), 'yyyy-MM-dd')
and dt<='{{ execution_date | cst_ds }}' and from_unixtime(unix_timestamp(plan_sign_date),'yyyy-MM-dd')>=date_sub('{{ execution_date | cst_ds }}',10)
and from_unixtime(unix_timestamp(plan_sign_date),'yyyy-MM-dd')<=date_add('{{ execution_date | cst_ds }}',3)
GROUP BY from_unixtime(unix_timestamp(plan_sign_date),'yyyy-MM-dd')
  ,island
  ,nvl(island_code,'--')
  ,nvl(island_name,'--')
  ,districtprescription
  ,start_pick_network_code
  ,sign_network_code
  ,start_subordinate_agent_code
  ,start_subordinate_agent_name
  ,start_regional_id
  ,start_regional_desc
  ,start_provider_id
  ,start_provider_desc
  ,sign_subordinate_agent_code
  ,sign_subordinate_agent_name
  ,sign_regional_id
  ,sign_regional_desc
  ,sign_provider_id
  ,sign_provider_desc
  ,is_pres_sign
  ,is_real_sign
  ,economic_circle_type  -- 经济圈名称
  ,platform_effect_type  -- 是否平台时效
  ,platform_effect  -- 平台时效
  ,platform_name  --桃花岛平台时效名称
  ,plan_platform_date
  ,mountain_platform_name  --紫金山平台时效名称
  ,plan_mountain_platform_date
  ,mountain_platform_effect  -- 紫金山平台时效
  ,free_platform_name  --逍遥锋平台时效名称
  ,plan_free_platform_date
  ,free_platform_effect  -- 逍遥锋平台时效
  ,start_manage_regional_code  -- 始发管理大区code',
  ,start_manage_regional_name  -- 始发管理大区name',
  ,sign_manage_regional_code  -- 签收管理大区code',
  ,sign_manage_regional_name -- 签收管理大区name',
  ,jd_platform_name  --京东平台时效名称
  ,jd_platform_effect  -- 京东平台时效
  ,date(plan_jd_platform_date)
  ,cainiao_platform_name  --菜鸟平台时效名称
  ,cainiao_platform_effect  -- 菜鸟平台时效
  ,plan_cainiao_platform_date
  ,cainiao_pro_platform_name  --菜鸟超时平台时效名称
  ,cainiao_pro_platform_effect  -- 菜鸟超时平台时效
  ,plan_cainiao_pro_platform_date
  ,express_type_code
  ,express_type_name
  ,customer_code
  ,customer_name
  ,mountain_pro_platform_name  --紫金山平台时效名称
  ,mountain_pro_platform_effect  -- 紫金山平台时效
  ,plan_mountain_pro_platform_date
  distribute by dt, PMOD(HASH(RAND()),80);

insert overwrite table bidefault.dm_agency_province_pre_reach_rate_sign_dt partition(dt)
select dt as date_time  -- "日期",
  ,nvl(island_code,'--') -- "订单来源",
  ,districtprescription -- "区县时效",
  ,is_pres_sign -- "时效签收",
  ,is_real_sign -- "实际签收",
  ,start_subordinate_agent_code -- "始发代理区code",
  ,sign_subordinate_agent_code -- "签收代理区code",
  ,start_provider_id -- "始发省份id",
  ,sign_provider_id -- "签收省份id",
  ,start_manage_regional_code  -- 始发管理大区code',
  ,start_manage_regional_name  -- 始发管理大区name',
  ,start_subordinate_agent_name -- "始发代理区name",
  ,start_regional_id -- "始发大区id",
  ,start_regional_desc -- "始发大区name",
  ,start_provider_desc -- "始发省份name",
  ,sign_manage_regional_code  -- 签收管理大区code',
  ,sign_manage_regional_name -- 签收管理大区name',
  ,sign_subordinate_agent_name -- "签收代理区name",
  ,sign_regional_id -- "签收大区id",
  ,sign_regional_desc -- "签收大区name",
  ,sign_provider_desc -- "签收省份name",
  ,nvl(island_name,'--')
  ,economic_circle_type  -- 经济圈名称
  ,sum(case when is_real_standard=1 and is_real_sign=1 then 1 else 0 end) as real_reach_sum -- "实际达成票数",
  ,sum(case when (is_real_standard=0 or is_real_standard is null) and (is_real_sign=1 or is_real_sign=0) then 1 else 0 end) as real_no_reach_sum -- "实际未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) then 1 else 0 end) as real_total -- "实际总票量",
  ,sum(case when is_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as pres_reach_sum -- "时效达成票数",
  ,sum(case when (is_pres_standard=0 or is_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) then 1 else 0 end) as pres_no_reach_sum -- "时效未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) then 1 else 0 end) as pres_total -- "时效总票量",
  ,sum(case when datediff(real_sign_time,taking_date)=0 then 1 else 0 end) as real_zeroday_num   --实际当日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=1 then 1 else 0 end) as real_oneday_num    --实际次日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=2 then 1 else 0 end) as real_twoday_num    --实际隔日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=3 then 1 else 0 end) as real_threeday_num  --实际三日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=4 then 1 else 0 end) as real_four_num      --实际四日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=0 then 1 else 0 end) as pres_zeroday_num  --时效当日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=1 then 1 else 0 end) as pres_oneday_num  --时效次日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=2 then 1 else 0 end) as pres_twoday_num  --时效隔日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=3 then 1 else 0 end) as pres_threeday_num  --时效三日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=4 then 1 else 0 end) as pres_four_num  --时效四日达票数
   --桃花岛平台时效
  ,platform_effect_type  -- 是否平台时效
  ,sum(case when is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as real_platform_reach_sum -- "实际桃花岛达成票数",
  ,sum(case when (is_platform_real_standard=0 or is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and platform_effect_type=1 then 1 else 0 end) as real_platform_no_reach_sum -- "实际桃花岛未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and platform_effect_type=1 then 1 else 0 end) as real_platform_total -- "实际桃花岛总票量",
  ,sum(case when is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as pres_platform_reach_sum -- "时效桃花岛达成票数",
  ,sum(case when (is_platform_pres_standard=0 or is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and platform_effect_type=1 then 1 else 0 end) as pres_platform_no_reach_sum -- "时效桃花岛未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and platform_effect_type=1 then 1 else 0 end) as pres_platform_total -- "时效桃花岛总票量",
  ,platform_name  --桃花岛平台时效名称
  ,platform_effect  -- 平台时效
  ,plan_platform_date
  --紫金山平台时效
  ,sum(case when mountain_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as mountain_real_platform_reach_sum -- "实际紫金山达成票数",
  ,sum(case when (mountain_is_platform_real_standard=0 or mountain_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_real_platform_no_reach_sum -- "实际紫金山未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_real_platform_total -- "实际紫金山总票量",
  ,sum(case when mountain_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as mountain_pres_platform_reach_sum -- "时效紫金山达成票数",
  ,sum(case when (mountain_is_platform_pres_standard=0 or mountain_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_pres_platform_no_reach_sum -- "时效紫金山未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and mountain_platform_effect_type=1 then 1 else 0 end) as mountain_pres_platform_total -- "时效紫金山总票量",
  ,mountain_platform_name  --紫金山平台时效名称
  ,mountain_platform_effect  -- 紫金山平台时效
  ,plan_mountain_platform_date
  --逍遥峰平台时效
  ,sum(case when free_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as free_real_platform_reach_sum -- "实际逍遥峰达成票数",
  ,sum(case when (free_is_platform_real_standard=0 or free_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and free_platform_effect_type=1 then 1 else 0 end) as free_real_platform_no_reach_sum -- "实际逍遥峰未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and free_platform_effect_type=1 then 1 else 0 end) as free_real_platform_total -- "实际逍遥峰总票量",
  ,sum(case when free_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as free_pres_platform_reach_sum -- "时效逍遥峰达成票数",
  ,sum(case when (free_is_platform_pres_standard=0 or free_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and free_platform_effect_type=1 then 1 else 0 end) as free_pres_platform_no_reach_sum -- "时效逍遥峰未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and free_platform_effect_type=1 then 1 else 0 end) as free_pres_platform_total -- "时效逍遥峰总票量",
  ,free_platform_name  --逍遥峰平台时效名称
  ,free_platform_effect  -- 逍遥锋平台时效
  ,plan_free_platform_date
    --京东平台时效
  ,sum(case when jd_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as jd_real_platform_reach_sum -- "实际京东达成票数",
  ,sum(case when (jd_is_platform_real_standard=0 or jd_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and jd_platform_effect_type=1 then 1 else 0 end) as jd_real_platform_no_reach_sum -- "实际京东未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and jd_platform_effect_type=1 then 1 else 0 end) as jd_real_platform_total -- "实际京东总票量",
  ,sum(case when jd_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as jd_pres_platform_reach_sum -- "时效京东达成票数",
  ,sum(case when (jd_is_platform_pres_standard=0 or jd_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and jd_platform_effect_type=1 then 1 else 0 end) as jd_pres_platform_no_reach_sum -- "时效京东未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and jd_platform_effect_type=1 then 1 else 0 end) as jd_pres_platform_total -- "时效京东总票量",
  ,jd_platform_name  --京东平台时效名称
  ,jd_platform_effect  -- 逍遥锋平台时效
  ,date(plan_jd_platform_date) plan_jd_platform_date
    --菜鸟平台时效
  ,sum(case when cainiao_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as cainiao_real_platform_reach_sum -- "实际菜鸟达成票数",
  ,sum(case when (cainiao_is_platform_real_standard=0 or cainiao_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_real_platform_no_reach_sum -- "实际菜鸟未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_real_platform_total -- "实际菜鸟总票量",
  ,sum(case when cainiao_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as cainiao_pres_platform_reach_sum -- "时效菜鸟达成票数",
  ,sum(case when (cainiao_is_platform_pres_standard=0 or cainiao_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_pres_platform_no_reach_sum -- "时效菜鸟未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and cainiao_platform_effect_type=1 then 1 else 0 end) as cainiao_pres_platform_total -- "时效菜鸟总票量",
  ,cainiao_platform_name  --菜鸟平台时效名称
  ,cainiao_platform_effect  -- 逍遥锋平台时效
  ,plan_cainiao_platform_date
 --菜鸟超时平台时效
  ,sum(case when cainiao_pro_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as cainiao_pro_real_platform_reach_sum -- "实际菜鸟达成票数",
  ,sum(case when (cainiao_pro_is_platform_real_standard=0 or cainiao_pro_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_real_platform_no_reach_sum -- "实际菜鸟未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_real_platform_total -- "实际菜鸟总票量",
  ,sum(case when cainiao_pro_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as cainiao_pro_pres_platform_reach_sum -- "时效菜鸟达成票数",
  ,sum(case when (cainiao_pro_is_platform_pres_standard=0 or cainiao_pro_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_pres_platform_no_reach_sum -- "时效菜鸟未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and cainiao_pro_platform_effect_type=1 then 1 else 0 end) as cainiao_pro_pres_platform_total -- "时效菜鸟总票量",
  ,cainiao_pro_platform_name  --菜鸟超时平台时效名称
  ,cainiao_pro_platform_effect  -- 菜鸟超时平台时效
  ,plan_cainiao_pro_platform_date
  ,express_type_code
  ,express_type_name
  ,sum(case when datediff(real_sign_time,taking_date)=5 then 1 else 0 end) as real_five_num    --实际五日达票数
  ,sum(case when datediff(real_sign_time,taking_date)=6 then 1 else 0 end) as real_six_num  --实际六日达票数
  ,sum(case when datediff(real_sign_time,taking_date)>6 then 1 else 0 end) as real_six_up_num  --实际六日以上达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=5 then 1 else 0 end) as pre_five_num  --时效五日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)=6 then 1 else 0 end) as pre_six_num  --时效六日达票数
  ,sum(case when datediff(pres_sign_time,taking_date)>6 then 1 else 0 end) as pre_six_up_num  --时效六日以上达票数
  ,sum(if(cainiao_slower_pre=1,1,0)) as cainiao_slower_pre_num --菜鸟慢于平台的票数
  ,customer_code
  ,customer_name
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>0 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=24,1,0)) as jd_real_one_num    --实际24票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>24 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=36 ,1,0)) as jd_real_two_num  --实际36票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>36 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=48 ,1,0)) as jd_real_three_num  --实际48票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>48 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=60 ,1,0)) as jd_real_four_num  --实际60票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>60 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=72 ,1,0)) as jd_real_five_num  --实际72票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>72 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=96 ,1,0)) as jd_real_six_num  --实际96票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>96 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600<=120,1,0)) as jd_real_seven_num  --实际120票数
  ,sum(if(is_real_sign=1 and (unix_timestamp(real_sign_time)-unix_timestamp(taking_time))/3600>120,1,0)) as jd_real_eight_num    --实际大于120票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>0 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=24,1,0)) as jd_pres_one_num      --实际24票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>24 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=36 ,1,0)) as jd_pres_two_num    --实际36票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>36 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=48 ,1,0)) as jd_pres_three_num  --实际48票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>48 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=60 ,1,0)) as jd_pres_four_num   --实际60票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>60 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=72 ,1,0)) as jd_pres_five_num   --实际72票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>72 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=96 ,1,0)) as jd_pres_six_num    --实际96票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>96 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600<=120,1,0)) as jd_pres_seven_num  --实际120票数
  ,sum(if(is_pres_sign=1 and (unix_timestamp(pres_sign_time)-unix_timestamp(taking_time))/3600>120,1,0)) as jd_pres_eight_num    --实际大于120票数
  ,sum(if(cainiao_pro_real_effect_diff=2,1,0)) cainiao_pro_real_pre_two_num
  ,sum(if(cainiao_pro_real_effect_diff=1,1,0)) cainiao_pro_real_pre_one_num
  ,sum(if(cainiao_pro_real_effect_diff=-1,1,0)) cainiao_pro_real_late_one_num
  ,sum(if(cainiao_pro_real_effect_diff=-2,1,0)) cainiao_pro_real_late_two_num
  ,sum(if(cainiao_pro_real_effect_diff<-2,1,0)) cainiao_pro_real_late_two_more_num
  ,sum(if(cainiao_pro_pres_effect_diff=2,1,0))  cainiao_pro_pres_pre_two_num
  ,sum(if(cainiao_pro_pres_effect_diff=1,1,0))  cainiao_pro_pres_pre_one_num
  ,sum(if(cainiao_pro_pres_effect_diff=-1,1,0)) cainiao_pro_pres_late_one_num
  ,sum(if(cainiao_pro_pres_effect_diff=-2,1,0)) cainiao_pro_pres_late_two_num
  ,sum(if(cainiao_pro_pres_effect_diff<-2,1,0)) cainiao_pro_pres_late_two_more_num
  --紫金山平台时效
  ,sum(case when mountain_pro_is_platform_real_standard=1 and is_real_sign=1 then 1 else 0 end) as mountain_pro_real_platform_reach_sum -- "实际紫金山达成票数",
  ,sum(case when (mountain_pro_is_platform_real_standard=0 or mountain_pro_is_platform_real_standard is null) and (is_real_sign=1 or is_real_sign=0)  and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_real_platform_no_reach_sum -- "实际紫金山未达成票数",
  ,sum(case when (is_real_sign=1 or is_real_sign=0) and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_real_platform_total -- "实际紫金山总票量",
  ,sum(case when mountain_pro_is_platform_pres_standard=1 and is_pres_sign=1 then 1 else 0 end) as mountain_pro_pres_platform_reach_sum -- "时效紫金山达成票数",
  ,sum(case when (mountain_pro_is_platform_pres_standard=0 or mountain_pro_is_platform_pres_standard is null) and (is_pres_sign=1 or is_pres_sign=0) and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_pres_platform_no_reach_sum -- "时效紫金山未达成票数",
  ,sum(case when (is_pres_sign=1 or is_pres_sign=0) and mountain_pro_platform_effect_type=1 then 1 else 0 end) as mountain_pro_pres_platform_total -- "时效紫金山总票量",
  ,mountain_pro_platform_name  --紫金山平台时效名称
  ,mountain_pro_platform_effect  -- 紫金山平台时效
  ,plan_mountain_pro_platform_date
  ,dt
FROM bidefault.dm_prescription_reach_details_sign_dt where dt>=date_sub('{{ execution_date | cst_ds }}',10) and dt<='{{ execution_date | cst_ds }}'
GROUP BY dt
  ,island
  ,nvl(island_code,'--')
  ,nvl(island_name,'--')
  ,districtprescription
  ,start_pick_network_code
  ,sign_network_code
  ,start_subordinate_agent_code
  ,start_subordinate_agent_name
  ,start_regional_id
  ,start_regional_desc
  ,start_provider_id
  ,start_provider_desc
  ,sign_subordinate_agent_code
  ,sign_subordinate_agent_name
  ,sign_regional_id
  ,sign_regional_desc
  ,sign_provider_id
  ,sign_provider_desc
  ,is_pres_sign
  ,is_real_sign
  ,economic_circle_type  -- 经济圈名称
  ,platform_effect_type  -- 是否平台时效
  ,platform_effect  -- 平台时效
  ,platform_name  --桃花岛平台时效名称
  ,plan_platform_date
  ,mountain_platform_name  --紫金山平台时效名称
  ,plan_mountain_platform_date
  ,mountain_platform_effect  -- 紫金山平台时效
  ,free_platform_name  --逍遥锋平台时效名称
  ,plan_free_platform_date
  ,free_platform_effect  -- 逍遥锋平台时效
  ,start_manage_regional_code  -- 始发管理大区code',
  ,start_manage_regional_name  -- 始发管理大区name',
  ,sign_manage_regional_code  -- 签收管理大区code',
  ,sign_manage_regional_name -- 签收管理大区name',
  ,jd_platform_name  --京东平台时效名称
  ,jd_platform_effect  -- 京东平台时效
  ,date(plan_jd_platform_date)
  ,cainiao_platform_name  --菜鸟平台时效名称
  ,cainiao_platform_effect  -- 菜鸟平台时效
  ,plan_cainiao_platform_date
  ,cainiao_pro_platform_name  --菜鸟超时平台时效名称
  ,cainiao_pro_platform_effect  -- 菜鸟超时平台时效
  ,plan_cainiao_pro_platform_date
  ,express_type_code
  ,express_type_name
  ,customer_code
  ,customer_name
  ,mountain_pro_platform_name  --紫金山平台时效名称
  ,mountain_pro_platform_effect  -- 紫金山平台时效
  ,plan_mountain_pro_platform_date
  distribute by dt, PMOD(HASH(RAND()),80);